package com.sharemarking.wa.platform.utils.excel;

import com.google.common.collect.Lists;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.context.ApplicationContext;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;

/**
 * 导入Excel文件（支持“XLS”和“XLSX”格式）
 */

public class ImportExcel {

    //static DictService dictService;

    //private Logger log = Logger.getLogger(ImportExcel.class);

    public static void setApplicationContext(ApplicationContext context) {
        //dictService = (DictService) context.getBean(DictService.class);
    }

    /**
     * 工作薄对象
     */
    private Workbook wb;

    /**
     * 工作表对象
     */
    private Sheet sheet;

    /**
     * 标题行号
     */
    private int headerNum;

    /**
     * 构造函数
     *
     * @param fileName
     *            导入文件，读取第一个工作表
     * @param headerNum
     *            标题行号，数据行号=标题行号+1
     * @throws InvalidFormatException
     * @throws IOException
     */
    public ImportExcel(String fileName, int headerNum)
            throws InvalidFormatException, IOException {
        this(new File(fileName), headerNum);
    }

    /**
     * 构造函数
     *
     * @param file
     *            导入文件对象，读取第一个工作表
     * @param headerNum
     *            标题行号，数据行号=标题行号+1
     * @throws InvalidFormatException
     * @throws IOException
     */
    public ImportExcel(File file, int headerNum) throws InvalidFormatException,
            IOException {
        this(file, headerNum, 0);
    }

    /**
     * 构造函数
     *
     * @param fileName
     *            导入文件
     * @param headerNum
     *            标题行号，数据行号=标题行号+1
     * @param sheetIndex
     *            工作表编号
     * @throws InvalidFormatException
     * @throws IOException
     */
    public ImportExcel(String fileName, int headerNum, int sheetIndex)
            throws InvalidFormatException, IOException {
        this(new File(fileName), headerNum, sheetIndex);
    }

    /**
     * 构造函数
     *
     * @param file
     *            导入文件对象
     * @param headerNum
     *            标题行号，数据行号=标题行号+1
     * @param sheetIndex
     *            工作表编号
     * @throws InvalidFormatException
     * @throws IOException
     */
    public ImportExcel(File file, int headerNum, int sheetIndex)
            throws InvalidFormatException, IOException {
        this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);
    }

    /**
     * 构造函数
     *
     * @param multipartFile
     *            导入文件对象
     * @param headerNum
     *            标题行号，数据行号=标题行号+1
     * @param sheetIndex
     *            工作表编号
     * @throws InvalidFormatException
     * @throws IOException
     */
    public ImportExcel(MultipartFile multipartFile, int headerNum,
                       int sheetIndex) throws InvalidFormatException, IOException {
        this(multipartFile.getOriginalFilename(), multipartFile
                .getInputStream(), headerNum, sheetIndex);
    }

    /**
     * 构造函数
     *
     * @param fileName
     *            导入文件对象
     * @param headerNum
     *            标题行号，数据行号=标题行号+1
     * @param sheetIndex
     *            工作表编号
     * @throws InvalidFormatException
     * @throws IOException
     */
    public ImportExcel(String fileName, InputStream is, int headerNum,
                       int sheetIndex) throws InvalidFormatException, IOException {
        if (StringUtils.isBlank(fileName)) {
            throw new RuntimeException("导入文档为空!");
        } else if (fileName.toLowerCase().endsWith("xls")) {
            this.wb = new HSSFWorkbook(is);
        } else if (fileName.toLowerCase().endsWith("xlsx")) {
            this.wb = new XSSFWorkbook(is);
        } else {
            throw new RuntimeException("文档格式不正确!");
        }
        if (this.wb.getNumberOfSheets() < sheetIndex) {
            throw new RuntimeException("文档中没有工作表!");
        }
        this.sheet = this.wb.getSheetAt(sheetIndex);
        this.headerNum = headerNum;

        //log.debug("Initialize success.");
    }

    /**
     * 获取行对象
     *
     * @param rownum
     * @return
     */
    public Row getRow(int rownum) {
        return this.sheet.getRow(rownum);
    }

    /**
     * 获取数据行号
     *
     * @return
     */
    public int getDataRowNum() {
    	//System.out.println("number"+headerNum);
        return headerNum + 1;
    }

    /**
     * 获取最后一个数据行号
     *
     * @return
     */
    public int getLastDataRowNum() {
        return this.sheet.getLastRowNum() + headerNum + 1;
    }

    /**
     * 获取最后一个列号
     *
     * @return
     */
    public int getLastCellNum() {
        return this.getRow(headerNum).getLastCellNum();
    }

    /**
     * 获取单元格值
     *
     * @param row
     *            获取的行
     * @param column
     *            获取单元格列号
     * @return 单元格值
     */
    @SuppressWarnings("deprecation")
	public Object getCellValue(Row row, int column) {
        Object val = "";
        try {
            Cell cell = row.getCell(column);
            if (cell != null) {
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    val = cell.getNumericCellValue();
                } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    val = cell.getStringCellValue();
                } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    val = cell.getCellFormula();
                } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                    val = cell.getBooleanCellValue();
                } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
                    val = cell.getErrorCellValue();
                }
            }
        } catch (Exception e) {
            return val;
        }
        return val;
    }
    
    /**
     * 描述：对表格中数值进行格式化
     *
     * @param cell
     * @return
     */
	public String getCellValue(Cell cell) {
        String value = null;
        //DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
        //SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
        //SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        if(cell == null) {
        	return "";
        }
        DecimalFormat df = new DecimalFormat("0");//格式化number String字符串
        switch (cell.getCellTypeEnum()) {
            case STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case NUMERIC:
            	value = df.format(cell.getNumericCellValue());
            	break;
            /*case Cell.CELL_TYPE_NUMERIC:
                if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    value = df.format(cell.getNumericCellValue());
                } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                    value = sdf.format(cell.getDateCellValue());
                } else {
                    //Sun Dec 31 15:03:00 CST 1899格式化时间15:03
                    value = sdf2.format(cell.getDateCellValue()).substring(11, 16);
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                //value = cell.getBooleanCellValue();
                value = "";
                break;
            case Cell.CELL_TYPE_BLANK:
                value = "";
                break;*/
            default:
                break;
        }
        return value;
    }

    /**
     * 获取导入数据列表
     *
     * @param cls
     *            导入对象类型
     * @param groups
     *            导入分组
     */
    public <E> List<E> getDataList(Class<E> cls, int... groups)
            throws InstantiationException, IllegalAccessException {
        List<Object[]> annotationList = Lists.newArrayList();
        // Get annotation field
        Field[] fs = cls.getDeclaredFields();
        for (Field f : fs) {
            ExcelField ef = f.getAnnotation(ExcelField.class);
            if (ef != null && (ef.type() == 0 || ef.type() == 2)) {
                if (groups != null && groups.length > 0) {
                    boolean inGroup = false;
                    for (int g : groups) {
                        if (inGroup) {
                            break;
                        }
                        for (int efg : ef.groups()) {
                            if (g == efg) {
                                inGroup = true;
                                annotationList.add(new Object[] { ef, f });
                                break;
                            }
                        }
                    }
                } else {
                    annotationList.add(new Object[] { ef, f });
                }
            }
        }
        // Get annotation method
        Method[] ms = cls.getDeclaredMethods();
        for (Method m : ms) {
            ExcelField ef = m.getAnnotation(ExcelField.class);
            if (ef != null && (ef.type() == 0 || ef.type() == 2)) {
                if (groups != null && groups.length > 0) {
                    boolean inGroup = false;
                    for (int g : groups) {
                        if (inGroup) {
                            break;
                        }
                        for (int efg : ef.groups()) {
                            if (g == efg) {
                                inGroup = true;
                                annotationList.add(new Object[] { ef, m });
                                break;
                            }
                        }
                    }
                } else {
                    annotationList.add(new Object[] { ef, m });
                }
            }
        }
        // Field sorting
        Collections.sort(annotationList, new Comparator<Object[]>() {
            @Override
            public int compare(Object[] o1, Object[] o2) {
                return new Integer(((ExcelField) o1[0]).sort())
                        .compareTo(new Integer(((ExcelField) o2[0]).sort()));
            };
        });
        List<E> dataList = Lists.newArrayList();
        for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) {
            E e = (E) cls.newInstance();
            int column = 0;
            Row row = this.getRow(i);
            StringBuilder sb = new StringBuilder();
            for (Object[] os : annotationList) {
                Object val = this.getCellValue(row, column++);
                if (val != null) {
                    ExcelField ef = (ExcelField) os[0];
                    // If is dict type, get dict value

                    if (StringUtils.isNotBlank(ef.dictType())) {
                        //val = dictService.getDictValue(val.toString(),
                          //      ef.dictType(), "");
                        // log.debug("Dictionary type value: ["+i+","+colunm+"] "
                        // + val);
                    }
                    // Get param type and type cast
                    Class<?> valType = Class.class;
                    if (os[1] instanceof Field) {
                        valType = ((Field) os[1]).getType();
                    } else if (os[1] instanceof Method) {
                        Method method = ((Method) os[1]);
                        if ("get".equals(method.getName().substring(0, 3))) {
                            valType = method.getReturnType();
                        } else if ("set".equals(method.getName()
                                .substring(0, 3))) {
                            valType = ((Method) os[1]).getParameterTypes()[0];
                        }
                    }
                    // log.debug("Import value type: ["+i+","+column+"] " +
                    // valType);
                    try {
                        if (valType == String.class) {
                            String s = String.valueOf(val.toString());
                            if (StringUtils.endsWith(s, ".0")) {
                                val = StringUtils.substringBefore(s, ".0");
                            } else {
                                val = String.valueOf(val.toString());
                                //val = String.valueOf(val.toString());
                            }
                        } else if (valType == Integer.class) {
                            val = Double.valueOf(val.toString()).intValue();
                        } else if (valType == Long.class) {
                            val = Double.valueOf(val.toString()).longValue();
                        } else if (valType == Double.class) {
                            val = Double.valueOf(val.toString());
                        } else if (valType == Float.class) {
                            val = Float.valueOf(val.toString());
                        } else if (valType == BigDecimal.class) {
                            val = BigDecimal.valueOf(Double.valueOf(val
                                    .toString()));
                        } else if (valType == Date.class) {
                            val = DateUtil.getJavaDate((Double) val);
                        } else {
                            if (ef.fieldType() != Class.class) {
                                val = ef.fieldType()
                                        .getMethod("getValue", String.class)
                                        .invoke(null, val.toString());
                            } else {
                                val = Class
                                        .forName(
                                                this.getClass()
                                                        .getName()
                                                        .replaceAll(
                                                                this.getClass()
                                                                        .getSimpleName(),
                                                                "fieldtype."
                                                                        + valType
                                                                        .getSimpleName()
                                                                        + "Field"))
                                        .getMethod("getValue", String.class)
                                        .invoke(null, val.toString());
                            }
                        }
                    } catch (Exception ex) {
                        //log.info("Get cell value [" + i + "," + column
                                //+ "] error: " + ex.toString());
                        val = null;
                    }
                    // set entity value
                    if (os[1] instanceof Field) {
                        Reflections.invokeSetter(e, ((Field) os[1]).getName(),
                                val);
                    } else if (os[1] instanceof Method) {
                        String mthodName = ((Method) os[1]).getName();
                        if ("get".equals(mthodName.substring(0, 3))) {
                            mthodName = "set"
                                    + StringUtils.substringAfter(mthodName,
                                    "get");
                        }
                        Reflections.invokeMethod(e, mthodName,
                                new Class[] { valType }, new Object[] { val });
                    }
                }
                sb.append(val + ", ");
            }
            dataList.add(e);
        }
        return dataList;
    }

    /**
     * 将流中的Excel数据转成List<Map>
     *
     * @param in       输入流
     * @param fileName 文件名（判断Excel版本）
     * @return
     * @throws Exception
     */
    public  List<Map<String,String>> parseExcel(InputStream in,String fileName,Integer titleNumber) throws Exception {
        // 根据文件名来创建Excel工作薄
        Workbook work = new HSSFWorkbook(in);
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
        // 返回数据
        List<Map<String,String>> allList = new ArrayList<>();
        // 遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }
            // 取第一行标题
            row = sheet.getRow(titleNumber-1);
            String title[] = null;
            if (row != null) {
                title = new String[row.getLastCellNum()];
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    title[y] = (String) getCellValue(cell);
                }
            } else {
                continue;
            }
            // 遍历当前sheet中的所有行
            for (int j = titleNumber; j < sheet.getLastRowNum() + 1; j++) {
                row = sheet.getRow(j);
                if (!getCellValue(row.getCell(0)).equals("")) {
                    Map<String,String> map = new HashMap<>();
                    // 遍历所有的列
                    for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                        cell = row.getCell(y);
                        String key = title[y];
                        map.put(key,getCellValue(cell));
                       /* if (key.equals("位置") && !getCellValue(cell).equals("")) {
                            sjbcExcelDTO.setBcName(getCellValue(cell));
                        }
                        if (key.equals("出勤\n时间") && !getCellValue(cell).equals("")) {
                            sjbcExcelDTO.setCqTime(getCellValue(cell));
                        }
                        if (key.equals("退勤时间") && !getCellValue(cell).equals("")) {
                            sjbcExcelDTO.setTqTime(getCellValue(cell));
                        }*/
                    }
                    allList.add(map);
                }
            }
        }
        work.close();
        return allList;
    }
}
